package com.twinkle.dbmanage.util;

import com.twinkle.dbmanage.vo.DBConfig;
import com.twinkle.dbmanage.vo.DBField;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Twinkle
 */
public class JDBCTemplate {
    private DBConfig dbConfig;
    private String[] header;

    public String[] getHeader() {
        return header;
    }

    public JDBCTemplate(DBConfig dbConfig) {
        this.dbConfig = dbConfig;
    }

    public int executeUpdate(String sql) {
        int rows = -1;
        Connection conn = null;
        PreparedStatement preStatement = null;
        try {
            conn = DBUtil.getConnection(dbConfig);
            preStatement = conn.prepareStatement(sql);
            rows = preStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(null, preStatement, conn);
        }
        return rows;
    }

    public List<Object[]> executeQuery(String sql) {
        List<Object[]> results = new ArrayList<>();
        Connection conn = null;
        PreparedStatement preStatement = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection(dbConfig);
            preStatement = conn.prepareStatement(sql);
            rs = preStatement.executeQuery();
            Object[] data;
            ResultSetMetaData rsMetaData = rs.getMetaData();
            header = new String[rsMetaData.getColumnCount()];
            for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
                header[i] = rsMetaData.getColumnName(i + 1);
            }
            while (rs.next()) {
                data = new Object[rsMetaData.getColumnCount()];
                for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
                    data[i] = rs.getObject(i + 1);
                }
                results.add(data);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rs, preStatement, conn);
        }
        return results;
    }

    public List<String> getTables(String dbName) {
        List<String> tableNameList = new ArrayList<>();
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection(dbConfig);
            DatabaseMetaData databaseMetaData = conn.getMetaData();
            rs = databaseMetaData.getTables(null, dbName, "%", new String[]{"TABLE"});
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                tableNameList.add(tableName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rs, null, conn);
        }
        return tableNameList;
    }

    public List<DBField> getFields(String dbName, String tableName) {

        List<DBField> fieldList = new ArrayList<>();
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection(dbConfig);
            DatabaseMetaData databaseMetaData = conn.getMetaData();
            rs = databaseMetaData.getColumns(null, dbName, tableName, null);
            while (rs.next()) {
                DBField df = new DBField();
                df.setFieldName(rs.getString("COLUMN_NAME"));
                df.setFieldSize(rs.getString("COLUMN_SIZE"));
                df.setFieldType(rs.getString("TYPE_NAME"));
                fieldList.add(df);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rs, null, conn);
        }
        return fieldList;
    }
}
